/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAL;

import BUSObject.Product;
import com.sun.rowset.CachedRowSetImpl;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.rowset.CachedRowSet;

/**
 *
 * @author HOANG ANH
 */
public class DABasic {

    private String sqlString = "jdbc:sqlserver://localhost:1443;databaseName=Soft_Drink_World;user=sa;password=a12345";
    private Connection conn;

    public DABasic() {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            conn = DriverManager.getConnection(sqlString);

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    private void OpenCon() {

        try {
            if (conn.isClosed()) {
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                conn = DriverManager.getConnection(sqlString);

            }
        } catch (Exception ex) {
            ex.printStackTrace();

        }
    }

    public PreparedStatement PreparedStatement(String query) throws SQLException {
        OpenCon();
        return conn.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    }

    public CallableStatement PrepareCall(String query) throws SQLException {
        OpenCon();
        CallableStatement stm = conn.prepareCall(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        return stm;
    }

    public CachedRowSet ExecuteResult(PreparedStatement preStm) throws SQLException {
       OpenCon();
        try {
            conn.setAutoCommit(false);

            ResultSet rs = preStm.executeQuery();

            conn.commit();
            conn.setAutoCommit(true);

            CachedRowSet crd = new CachedRowSetImpl();
            crd.populate(rs);
            rs.close();
            return crd;


        } catch (SQLException ex) {
            Logger.getLogger(DABasic.class.getName()).log(Level.SEVERE, null, ex);


            return null;
        } finally {
            if (preStm != null) {
                preStm.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
    }

    public CallableStatement ExecuteOutput(CallableStatement calStm) throws SQLException {
        OpenCon();
        try {
            calStm.execute();
            return calStm;


        } catch (SQLException ex) {
            Logger.getLogger(DABasic.class.getName()).log(Level.SEVERE, null, ex);


            return null;
        }
    }

    public int[] ExecuteBatch(PreparedStatement preStm) throws SQLException {
       OpenCon();
        try {
            conn.setAutoCommit(false);

            int[] output = preStm.executeBatch();

            conn.commit();
            conn.setAutoCommit(true);
            return output;


        } catch (SQLException ex) {
            Logger.getLogger(DABasic.class.getName()).log(Level.SEVERE, null, ex);


            return null;
        } finally {
            if (preStm != null) {
                preStm.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
    }

    public void Close(CallableStatement calStm) {
        if (calStm != null) {
            try {
                calStm.close();


            } catch (SQLException ex) {
                Logger.getLogger(DABasic.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        if (conn != null) {
            try {
                conn.close();


            } catch (SQLException ex) {
                Logger.getLogger(DABasic.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
}
